package com.insigma.yanwenlong.bookkeeping.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.insigma.yanwenlong.bookkeeping.db.jdbc.DBConnection;
import com.insigma.yanwenlong.bookkeeping.pojo.DayConsumeCondition;
import com.insigma.yanwenlong.bookkeeping.pojo.Detail;
import com.insigma.yanwenlong.bookkeeping.pojo.DetailCondition;
import com.insigma.yanwenlong.bookkeeping.pojo.YearConsumeCondition;
import com.insigma.yanwenlong.bookkeeping.pojo.vo.DetailVo;



public class DetailDaoImpl implements DetailDao{

		@Override
		public List<DetailVo> findDetails() throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "select * from detailview order by id";
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			List<DetailVo> details = new ArrayList<DetailVo>();
			while(resultSet.next()) {
				DetailVo detail = new DetailVo();
				detail.setId(resultSet.getInt("id"));
				detail.setCategoryId(resultSet.getInt("categoryId"));
				detail.setAccountId(resultSet.getInt("accountId"));
				detail.setHappenMoney(resultSet.getDouble("happenMoney"));
				detail.setMemberId(resultSet.getInt("memberId"));
				detail.setTranTime(resultSet.getDate("tranTime"));
				detail.setNote(resultSet.getString("note"));
				detail.setAccountName(resultSet.getString("accountName"));
				detail.setCategoryName(resultSet.getString("categoryName"));
				detail.setMemberName(resultSet.getString("memberName"));
				
				details.add(detail);
			}
			
			DBConnection.closeResultSet(resultSet);
			DBConnection.closeStatement(statement);
			DBConnection.closeConnection(connection);
			
			return details;
		}

		@Override
		public List<DetailVo> findDetailsBy(String accountName, Date startTime,
				Date stopTime, String categoryName,String note) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "select * from detailview where accountName = ? and tranTime between ? and ? and categoryName = ? and note = ? order by id";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, accountName);
			preparedStatement.setDate(2, startTime);
			preparedStatement.setDate(3, stopTime);
			preparedStatement.setString(4, categoryName);
			preparedStatement.setString(5, note);
			ResultSet resultSet = preparedStatement.executeQuery();
			List<DetailVo> details = new ArrayList<DetailVo>();
			while(resultSet.next()) {
				DetailVo detail = new DetailVo();
				detail.setId(resultSet.getInt("id"));
				detail.setCategoryId(resultSet.getInt("categoryId"));
				detail.setAccountId(resultSet.getInt("accountId"));
				detail.setHappenMoney(resultSet.getDouble("happenMoney"));
				detail.setMemberId(resultSet.getInt("memberId"));
				detail.setTranTime(resultSet.getDate("tranTime"));
				detail.setNote(resultSet.getString("note"));
				detail.setAccountName(resultSet.getString("accountName"));
				detail.setCategoryName(resultSet.getString("categoryName"));
				detail.setMemberName(resultSet.getString("memberName"));
				
				details.add(detail);
			}
			
			DBConnection.closeResultSet(resultSet);
			DBConnection.closeStatement(preparedStatement);
			DBConnection.closeConnection(connection);
			
			return details;
		}

		@Override
		public DetailVo finDetailVoById(int id) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "select * from detailview where id = ?";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			ResultSet resultSet = preparedStatement.executeQuery();
			DetailVo detail = new DetailVo();
			if(resultSet.next()) {
				detail.setId(resultSet.getInt("id"));
				detail.setCategoryId(resultSet.getInt("categoryId"));
				detail.setAccountId(resultSet.getInt("accountId"));
				detail.setHappenMoney(resultSet.getDouble("happenMoney"));
				detail.setMemberId(resultSet.getInt("memberId"));
				detail.setTranTime(resultSet.getDate("tranTime"));
				detail.setNote(resultSet.getString("note"));
				detail.setAccountName(resultSet.getString("accountName"));
				detail.setCategoryName(resultSet.getString("categoryName"));
				detail.setMemberName(resultSet.getString("memberName"));
			}
			
			DBConnection.closeResultSet(resultSet);
			DBConnection.closeStatement(preparedStatement);
			DBConnection.closeConnection(connection);
			
			return detail;
		}
		
		@Override
		public int updateDetailByDetailVo(DetailVo detailVo) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "update detail set accountId = ?,happenMoney = ?," +
					"categoryId = ?,tranTime = ?,memberId = ?,note = ? where id = ?";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, detailVo.getAccountId());
			preparedStatement.setDouble(2, detailVo.getHappenMoney());
			preparedStatement.setInt(3, detailVo.getCategoryId());
			preparedStatement.setDate(4, detailVo.getTranTime());
			preparedStatement.setInt(5, detailVo.getMemberId());
			preparedStatement.setString(6, detailVo.getNote());
			preparedStatement.setInt(7, detailVo.getId());
			
			int rowAffected = preparedStatement.executeUpdate();
			
			DBConnection.closeStatement(preparedStatement);
			DBConnection.closeConnection(connection);
			
			return rowAffected;
		}

		@Override
		public int deleteDetailById(int id) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "delete from detail where id = ?";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			
			int rowAffected = preparedStatement.executeUpdate();
			
			DBConnection.closeStatement(preparedStatement);
			DBConnection.closeConnection(connection);
			
			return rowAffected;
		}

		@Override
		public int addDetail(Detail detail) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql ="insert into detail(accountId,happenMoney," +
					"categoryId,tranTime,memberId,note) values(?,?,?,?,?,?)";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, detail.getAccountId());
			preparedStatement.setDouble(2, detail.getHappenMoney());
			preparedStatement.setInt(3, detail.getCategoryId());
			preparedStatement.setDate(4, detail.getTranTime());
			preparedStatement.setInt(5, detail.getMemberId());
			preparedStatement.setString(6, detail.getNote());
			
			int rowAffected = preparedStatement.executeUpdate();
			
			DBConnection.closeStatement(preparedStatement);
			DBConnection.closeConnection(connection);
			
			return rowAffected;
		}

		@Override
		public List<DetailVo> findDetailsBy(String accountName, Date startTime,
				Date stopTime, String memberName, double beginMoney,
				double endMoney) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "select * from detailview where accountName = ? and tranTime between ? and ? and memberName = ? and happenMoney between ? and ? order by id";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, accountName);
			preparedStatement.setDate(2, startTime);
			preparedStatement.setDate(3, stopTime);
			preparedStatement.setString(4, memberName);
			preparedStatement.setDouble(5, beginMoney);
			preparedStatement.setDouble(6, endMoney);
			ResultSet resultSet = preparedStatement.executeQuery();
			List<DetailVo> details = new ArrayList<DetailVo>();
			while(resultSet.next()) {
				DetailVo detail = new DetailVo();
				detail.setId(resultSet.getInt("id"));
				detail.setCategoryId(resultSet.getInt("categoryId"));
				detail.setAccountId(resultSet.getInt("accountId"));
				detail.setHappenMoney(resultSet.getDouble("happenMoney"));
				detail.setMemberId(resultSet.getInt("memberId"));
				detail.setTranTime(resultSet.getDate("tranTime"));
				detail.setNote(resultSet.getString("note"));
				detail.setAccountName(resultSet.getString("accountName"));
				detail.setCategoryName(resultSet.getString("categoryName"));
				detail.setMemberName(resultSet.getString("memberName"));
				
				details.add(detail);
			}
			
			DBConnection.closeResultSet(resultSet);
			DBConnection.closeStatement(preparedStatement);
			DBConnection.closeConnection(connection);
			
			return details;
		}

		@Override
		public List<DetailCondition> findDetailsByConditions(
				DetailCondition detailCondition) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "select * from detail d,category c where d.categoryId = c.id ";
			if(detailCondition.getAccountId() != -1) {
				sql += " and accountId = " + detailCondition.getAccountId();
				
			}
			
			if(!"".equals(detailCondition.getStartDate())) {
				sql += " and tranTime >= '" + detailCondition.getStartDate() + "'";
			}
			
			if(!"".equals(detailCondition.getStopDate())) {
				sql += " and tranTime <= '" + detailCondition.getStopDate() + "'";
			}
			
			if(detailCondition.getDirectionId() != -1) {
				sql += " and c.directionId = " + detailCondition.getDirectionId();
			}
			
			if(detailCondition.getCategoryId() != -1) {
				sql += " and c.id = " + detailCondition.getCategoryId();
			}
			
			if(!"".equals(detailCondition.getNote())) {
				sql += " and note like '%" + detailCondition.getNote() + "%'";
			}
			
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			
			List<DetailCondition> detailConditions = new ArrayList<DetailCondition>();
			while(resultSet.next()) {
				DetailCondition detailCondition2 = new DetailCondition();
				
				detailCondition2.setAccountId(resultSet.getInt("accountId"));
				detailCondition2.setCategoryId(resultSet.getInt("categoryId"));
				detailCondition2.setDirectionId(resultSet.getInt("directionId"));
				detailCondition2.setNote(resultSet.getString("note"));
				detailCondition2.setTranTime(resultSet.getDate("tranTime"));
				detailCondition2.setHappenMoney(resultSet.getDouble("happenMoney"));
				detailCondition2.setId(resultSet.getInt("id"));
				detailCondition2.setMemberId(resultSet.getInt("memberId"));
				
				detailConditions.add(detailCondition2);
			}
			DBConnection.closeResultSet(resultSet);
			DBConnection.closeStatement(statement);
			DBConnection.closeConnection(connection);
			
			return detailConditions;
		}

		@Override
		public List<DayConsumeCondition> findDayConsumeConditions(
				DayConsumeCondition dcc) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "select * from detail where 1 = 1 ";
			
			if(dcc.getAccountId() != -1) {
				sql += " and accountId = " + dcc.getAccountId();
			}
			
			if(!"".equals(dcc.getBeginTime())) {
				sql += " and tranTime >= '" + dcc.getBeginTime() + "'";
			}
			
			if(!"".equals(dcc.getEndTime())) {
				sql += " and tranTime <= '" + dcc.getEndTime() + "'";
			}
			
			if(dcc.getMemberId() != -1) {
				sql += " and memberId = " + dcc.getMemberId();
			}
			
			if (0 != dcc.getStarMoney()) {
				sql += " and happenMoney >= " + dcc.getStarMoney();
			}
			
			if (0 != dcc.getEndMoney()) {
				sql += " and happenMoney <= " + dcc.getEndMoney();
			}
			
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			
			List<DayConsumeCondition> dccs = new ArrayList<DayConsumeCondition>();
			while(resultSet.next()) {
				DayConsumeCondition dcc1 = new DayConsumeCondition();
				dcc1.setCategoryId(resultSet.getInt("categoryId"));
				dcc1.setHappenMoney(resultSet.getDouble("happenMoney"));
				
				dccs.add(dcc1);
			}
			DBConnection.closeResultSet(resultSet);
			DBConnection.closeStatement(statement);
			DBConnection.closeConnection(connection);
			
			return dccs;
		}

		@Override
		public List<YearConsumeCondition> findYearConsumeConditions(
				YearConsumeCondition ycc) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "select * from yearconsumeview where year = " + ycc.getYear();
			
			if(ycc.getAccountId() != -1) {
				sql += " and accountId = " + ycc.getAccountId();
			}
			
			if(ycc.getMemberId() != -1) {
				sql += " and memberId = " + ycc.getMemberId();
			}
			
			if (0 != ycc.getBeginMoney()) {
				sql += " and happenMoney >= " + ycc.getBeginMoney();
			}
			
			if (0 != ycc.getEndMoney()) {
				sql += " and happenMoney <= " + ycc.getEndMoney();
			}
			
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			
			List<YearConsumeCondition> yccs = new ArrayList<YearConsumeCondition>();
			while(resultSet.next()) {
				YearConsumeCondition ycc1 = new YearConsumeCondition();
				
				ycc1.setCategoryId(resultSet.getInt("categoryId"));
				ycc1.setMonth1(resultSet.getDouble("month1"));
				ycc1.setMonth2(resultSet.getDouble("month2"));
				ycc1.setMonth3(resultSet.getDouble("month3"));
				ycc1.setMonth4(resultSet.getDouble("month4"));
				ycc1.setMonth5(resultSet.getDouble("month5"));
				ycc1.setMonth6(resultSet.getDouble("month6"));
				ycc1.setMonth7(resultSet.getDouble("month7"));
				ycc1.setMonth8(resultSet.getDouble("month8"));
				ycc1.setMonth9(resultSet.getDouble("month9"));
				ycc1.setMonth10(resultSet.getDouble("month10"));
				ycc1.setMonth11(resultSet.getDouble("month11"));
				ycc1.setMonth12(resultSet.getDouble("month12"));
				
				yccs.add(ycc1);
			}
			
			DBConnection.closeResultSet(resultSet);
			DBConnection.closeStatement(statement);
			DBConnection.closeConnection(connection);
			
			return yccs;
		}

		@Override
		public boolean deleteDetailBymemberId(int memberId) throws SQLException {
			Connection connection = DBConnection.getConnection();
			String sql = "delete from detail where memberId = ?";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, memberId);
			
			boolean isSuccess = preparedStatement.execute();
			
			DBConnection.closeStatement(preparedStatement);
			DBConnection.closeConnection(connection);
			
			return isSuccess;
		}
		
	

}
